package sc.tool.sqlgen.branchinfo.pfs;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.Validate;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import sc.prototype.common.ConsoleUtil;
import sc.prototype.common.Utils;
import sc.tool.sqlgen.branchinfo.BranchInfo;
import sc.tool.sqlgen.branchinfo.XlsxUtil;
import sc.tool.sqlgen.branchinfo.pfs.stringpro.OmitHeadProcessor;
import sc.tool.sqlgen.branchinfo.pfs.stringpro.ReplaceProcessor;
import sc.tool.sqlgen.branchinfo.pfs.stringpro.StringProcessor;

public class BranchInfoUpdateProcessor implements ConsoleUtil {

    public static void main(String[] args) {
        try {
            BranchInfoUpdateProcessor processor = new BranchInfoUpdateProcessor();
            processor.bankFilePath = "D:/WeibopaySVN/basis_doc/PPS/3_详细设计/支付前置-对外基础服务-省份城市地区银行.xlsx";
            processor.bankFileSheet = "银行信息";
            processor.changeFilePath = "D:/WeibopayMemo/出款服务FOS/branh_info_new.xlsx";
            processor.changeFileSheet = "更新分支行";
            processor.areaFilePath = "D:/WeibopayMemo/出款服务FOS/area.xlsx";
            processor.areaFileSheet = "area";
            processor.outputFilePath = "D:/WeibopayMemo/出款服务FOS/branh_info_update.xlsx";
            processor.process();
        } catch (Exception e) {
            Console.error("", e);
        }
    }

    /** 银行名称 */
    public static final int BANK_SHEET_NAME = 0;
    /** 银行编码 */
    public static final int BANK_SHEET_CODE = 1;
    /** 银行名称 */
    public static final int BANK_SHEET_ALIASES = 2;
    /** 行别代码 */
    public static final int BANK_SHEET_UNIFIED_NO = 3;

    String bankFilePath;
    String bankFileSheet;

    /** 城市名称 */
    public static final int AREA_SHEET_CITYNAME = 2;
    /** 省份名称 */
    public static final int AREA_SHEET_PROVNAME = 3;
    /** 城市Id */
    public static final int AREA_SHEET_CITYID = 4;
    /** 省份Id */
    public static final int AREA_SHEET_PROVID = 5;

    String areaFilePath;
    String areaFileSheet;

    /** 联行号 */
    public static final int CHANGE_SHEET_BRANCH_NO = 0;
    /** 差异内容 */
    public static final int CHANGE_SHEET_TYPE = 1;
    /** 原信息 */
    public static final int CHANGE_SHEET_OLD = 2;
    /** 新信息 */
    public static final int CHANGE_SHEET_NEW = 3;
    /** 新简称 */
    public static final int CHANGE_SHEET_SHORTNAME = 5;
    /** 行别代号 */
    public static final int CHANGE_SHEET_BANK_NO = 6;
    /** 银行编码 */
    public static final int CHANGE_SHEET_BANKCODE = 7;
    /** 解析得银行编码 */
    public static final int CHANGE_SHEET_BANKCODE_PARSED = 8;
    /** 省份Id */
    public static final int CHANGE_SHEET_PROVID = 9;
    /** 城市Id */
    public static final int CHANGE_SHEET_CITYID = 10;

    String changeFilePath;
    String changeFileSheet;
    int changeStartRowIndex = 1;
    int changeParseRowCount = 100000000;

    /** 分支行简称生成太短预警值 */
    public static final int PARSE_SHORTNAME_WARNING_SHORT = 3;
    /** 分支行简称生成太长预警值 */
    public static final int PARSE_SHORTNAME_WARNING_LONG = 13;

    String outputFilePath;

    /** 启用别名判断银行编码的行别代码 */
    public static final List<String> USE_ALIAS_UNIFIED_BANK_NO = new ArrayList<String>();//Arrays.asList("313", "314", "401", "402", "");

    public void process() {
        //加载银行
        loadBankInfos();

        //加载省份城市
        loadAreaInfo();

        //        if (true) {
        //            return;
        //        }

        //加载差异
        Sheet changeSheet = loadChangeSheet();
        for (Iterator<Row> iter = changeSheet.rowIterator(); iter.hasNext();) {
            Row row = iter.next();
            if (changeStartRowIndex >= 1) {
                changeStartRowIndex--;
                continue;
            }
            if (changeParseRowCount-- <= 0) {
                break;
            }
            //解析分支行信息
            BranchInfo branchInfo = getBranchInfo(row);
            //解析银行编码
            String bankCode = parseBankCode(branchInfo);
            //生成简称
            String alias = parseShortName(branchInfo);
            //写入别名
            writeParseResult(row, alias, bankCode);
        }

        //打印统计信息
        statistic.printStatistic();

        //保存文件
        writeBackFile(changeSheet);
    }

    List<BankInfo> banks;
    List<CityInfo> citys;
    List<ProvInfo> provs;

    SimpleStatistic statistic = new SimpleStatistic();

    /** 加载银行 */
    void loadBankInfos() {
        banks = new ArrayList<BankInfo>();
        List<String[]> rows = XlsxUtil.loadItems(bankFilePath, bankFileSheet, 1);
        for (String[] row : rows) {
            try {
                BankInfo bankInfo = new BankInfo();
                bankInfo.setBankAliases(ListUtil.splitString(row[BANK_SHEET_ALIASES], ",|，", true));
                bankInfo.setBankCode(row[BANK_SHEET_CODE]);
                String bankName = row[BANK_SHEET_NAME];
                bankInfo.setBankName(bankName);
                if (StringUtils.isNotEmpty(bankName) && !bankInfo.getBankAliases().contains(bankName)) {
                    bankInfo.getBankAliases().add(bankName);
                }
                if (row.length > BANK_SHEET_UNIFIED_NO) {
                    bankInfo.setUnifiedBankNo(row[BANK_SHEET_UNIFIED_NO]);
                } else {
                    bankInfo.setUnifiedBankNo("");
                }
                Collections.sort(bankInfo.getBankAliases(), StringLengthComparator.DESC_INSTANCE);
                Validator.check(bankInfo);
                banks.add(bankInfo);
            } catch (Exception e) {
                throw new RuntimeException("Illegal BankInfo[" + Utils.toString(row) + "]", e);
            }
        }
        Console.info("Bank loaded: {}", banks);
    }

    /** 加载省份城市 */
    void loadAreaInfo() {
        citys = new ArrayList<CityInfo>();
        provs = new ArrayList<ProvInfo>();
        List<String[]> rows = XlsxUtil.loadItems(areaFilePath, areaFileSheet, 1);
        for (String[] row : rows) {
            try {
                CityInfo cityNow = null;
                for (CityInfo city : citys) {
                    if (city.getCityName().equals(row[AREA_SHEET_CITYNAME])) {
                        cityNow = city;
                    }
                }
                ProvInfo provNow = null;
                for (ProvInfo prov : provs) {
                    if (prov.getProvName().equals(row[AREA_SHEET_PROVNAME])) {
                        provNow = prov;
                    }
                }
                if (cityNow == null) {
                    cityNow = new CityInfo();
                    cityNow.setCityId(row[AREA_SHEET_CITYID]);
                    cityNow.setCityName(row[AREA_SHEET_CITYNAME]);
                    cityNow.setCityAliases(Arrays.asList(cityNow.getCityName(),
                            cityNow.getCityName().replaceAll("市", "")));
                    Collections.sort(cityNow.getCityAliases(), StringLengthComparator.DESC_INSTANCE);
                    Validator.check(cityNow);
                    citys.add(cityNow);
                    if (provNow == null) {
                        provNow = new ProvInfo();
                        provNow.setProvId(row[AREA_SHEET_PROVID]);
                        provNow.setProvName(row[AREA_SHEET_PROVNAME]);
                        provNow.setProvAliases(Arrays.asList(provNow.getProvName(),
                                provNow.getProvName().replaceAll("市|省", "")));
                        Collections.sort(provNow.getProvAliases(), StringLengthComparator.DESC_INSTANCE);
                        Validator.check(provNow);
                        provs.add(provNow);
                    }
                    provNow.getCitys().add(cityNow);
                }
            } catch (Exception e) {
                throw new RuntimeException("Illegal AreaInfo[" + Utils.toString(row) + "]", e);
            }
        }
        Console.info("Area loaded: {}", provs);
    }

    /** 加载差异 */
    Sheet loadChangeSheet() {
        try {
            InputStream inp = new FileInputStream(changeFilePath);
            Workbook wb = new XSSFWorkbook(inp);
            return wb.getSheet(changeFileSheet);
        } catch (Exception e) {
            throw new RuntimeException("", e);
        }
    }

    /** 获取单元格内容, getCellString */
    String gcs(Row row, int cellnum) {
        return row.getCell(cellnum).getStringCellValue();
    }

    /** 只处理全称差异，否则异常 */
    BranchInfo getBranchInfo(Row row) {
        try {
            BranchInfo branchInfo = new BranchInfo();
            branchInfo.setBranchId(gcs(row, CHANGE_SHEET_BRANCH_NO));
            if (!"BRANCHNAME".equals(gcs(row, CHANGE_SHEET_TYPE))) {
                throw new IllegalArgumentException("只能处理全称不同差异！");
            }
            branchInfo.setBranchName(gcs(row, CHANGE_SHEET_NEW));
            branchInfo.setUnifiedBankId(gcs(row, CHANGE_SHEET_BANK_NO));
            branchInfo.setBankCode(gcs(row, CHANGE_SHEET_BANKCODE));
            branchInfo.setProvId(gcs(row, CHANGE_SHEET_PROVID));
            branchInfo.setCityId(gcs(row, CHANGE_SHEET_CITYID));
            Validator.check(branchInfo);
            return branchInfo;
        } catch (Exception e) {
            throw new RuntimeException("Illegal BranchInfo[" + Utils.toString(row) + "]", e);
        }
    }

    String parseBankCode(BranchInfo branchInfo) {
        BankInfo bankInfo = null;
        if (USE_ALIAS_UNIFIED_BANK_NO.contains(branchInfo.getUnifiedBankId())) {
            //特别判断银行编码
            for (BankInfo bank : banks) {
                for (String alias : bank.getBankAliases()) {
                    if (branchInfo.getBranchName().contains(alias)) {
                        bankInfo = bank;
                    }
                }
            }
        } else {
            //直接根据行别代码找银行
            for (BankInfo bank : banks) {
                if (branchInfo.getUnifiedBankId().equals(bank.getUnifiedBankNo())) {
                    bankInfo = bank;
                }
            }
        }
        if (bankInfo == null) {
            throw new IllegalArgumentException("未解析出银行信息: " + branchInfo);
        }
        if (!branchInfo.getBankCode().equals(bankInfo.getBankCode())) {
            statistic.addStatistic(String.format("银行编码：%s -> %s", branchInfo.getBankCode(), bankInfo.getBankCode()));
        }
        return bankInfo.getBankCode();
    }

    public List<StringProcessor> createAliasProcessors(BankInfo bank, ProvInfo prov, CityInfo city) {
        List<StringProcessor> processors = new ArrayList<StringProcessor>();
        //        processors.add(new ProcessOmitProcessor("分理处"));

        processors.add(new OmitHeadProcessor("股份有限公司"));
        processors.add(new OmitHeadProcessor("自治区"));
        processors.add(new ReplaceProcessor("农村商业银行股份有限公司", ""));
        processors.add(new ReplaceProcessor("农村信用联社股份有限公司", ""));
        processors.add(new ReplaceProcessor("村镇银行有限责任公司", ""));
        processors.add(new ReplaceProcessor("农村信用合作联社", ""));
        processors.add(new ReplaceProcessor("农村合作银行", ""));
        processors.add(new ReplaceProcessor("农村商业银行", ""));

        //        processors.add(new OmitHeadProcessor("农商银行"));
        //        processors.add(new OmitHeadProcessor("农村信用合作联社"));
        for (String bankAlias : bank.getBankAliases()) {
            processors.add(new ReplaceProcessor(bankAlias, ""));
        }
        for (String provAlias : prov.getProvAliases()) {
            processors.add(new ReplaceProcessor(provAlias, ""));
        }
        for (String cityAlias : city.getCityAliases()) {
            processors.add(new ReplaceProcessor(cityAlias, ""));
        }
        //        processors.add(new ReplaceProcessor("农村商业银行", "农商行"));
        //        processors.add(new ReplaceProcessor("农村信用合作联社", "农信社"));
        return processors;
    }

    /**
     * 生成分支行简称
     * 策略：
     * 1. 替换银行别名
     * 2. 替换省份别名
     * 3. 替换城市别名
     */
    String parseShortName(BranchInfo branchInfo) {
        try {
            String shortName = branchInfo.getBranchName();
            BankInfo branchBank = null;
            ProvInfo branchProv = null;
            CityInfo branchCity = null;
            for (BankInfo bank : banks) {
                if (bank.getBankCode().equals(branchInfo.getBankCode())) {
                    branchBank = bank;
                }
            }
            Validate.notNull(branchBank, "分支行银行信息不存在！");
            for (ProvInfo prov : provs) {
                if (prov.getProvId().equals(branchInfo.getProvId())) {
                    branchProv = prov;
                }
            }
            Validate.notNull(branchProv, "分支行省份信息不存在！");
            for (CityInfo city : citys) {
                if (city.getCityId().equals(branchInfo.getCityId())) {
                    branchCity = city;
                }
            }
            Validate.notNull(branchProv, "分支行城市信息不存在！");
            List<StringProcessor> processors = createAliasProcessors(branchBank, branchProv, branchCity);
            for (StringProcessor processor : processors) {
                String tempShortName = processor.process(shortName, processors);
                if (tempShortName.length() >= 4) {
                    shortName = tempShortName;
                }
            }
            if (shortName.length() >= PARSE_SHORTNAME_WARNING_LONG) {
                Console.warn("简称生成过长警告，原[{}]，新[{}]", branchInfo.getBranchName(), shortName);
                statistic.addStatistic("简称生成过长警告-" + branchInfo.getBankCode());
                statistic.addStatistic("简称生成过长警告");
            } else if (shortName.length() <= PARSE_SHORTNAME_WARNING_SHORT) {
                Console.warn("简称生成过短警告，原[{}]，新[{}]", branchInfo.getBranchName(), shortName);
                statistic.addStatistic("简称生成过短警告-" + branchInfo.getBankCode());
                statistic.addStatistic("简称生成过短警告");
            }
            //        Console.info("分支行简称生成：{} -> {}", branchInfo.getBranchName(), shortName);
            return shortName;
        } catch (Exception e) {
            throw new RuntimeException("Illegal BranchInfo Relation[" + Utils.toString(branchInfo) + "]", e);
        }
    }

    void writeParseResult(Row row, String shortName, String bankCode) {
        row.createCell(CHANGE_SHEET_SHORTNAME, Cell.CELL_TYPE_STRING).setCellValue(shortName);
        row.createCell(CHANGE_SHEET_BANKCODE_PARSED, Cell.CELL_TYPE_STRING).setCellValue(bankCode);
    }

    void writeBackFile(Sheet sheet) {
        XlsxUtil.writeFile(outputFilePath, sheet.getWorkbook());
        Console.info("Output to file: {}", outputFilePath);
    }
}
